﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management;
using Microsoft.SqlServer.Management.Common;
using System.Collections.Specialized;
using System.Data;
using LKVMLMLib;
using System.Drawing;
using System.IO;
using System.Web;
using System.ServiceModel.Channels;
using System.Net;
using System.Text.RegularExpressions;
using System.Security.Cryptography;

namespace LKVService
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "SystemService" in code, svc and config file together.
    // NOTE: In order to launch WCF Test Client for testing this service, please select SystemService.svc or SystemService.svc.cs at the Solution Explorer and start debugging.
    public class SystemService
    {
        private string dbName = string.Empty;
        private SqlConnection sqlConn;
        private List<Database> dbList;
        public bool BackupDb(string dbName, string connection)
        {
            Backup dbBackup = new Backup();
            try
            {
                var sqlConn = new SqlConnection("Data Source=" + connection + ";Initial Catalog=" + dbName + ";User ID=sa;Password=Admin123456!");
                var sqlServer = new Server(new ServerConnection(sqlConn));
                dbBackup.Action = BackupActionType.Database;
                dbBackup.Database = dbName;
                dbBackup.BackupSetName = string.Format("{0} backup set.", dbName);
                dbBackup.BackupSetDescription = string.Format("Database: {0}. Date: {1}.", dbName, DateTime.Now.ToString("dd.MM.yyyy hh:m"));
                dbBackup.MediaDescription = "Disk";
                DateTime dt = DateTime.Now;
                var databaseName = "AnhQue_" + dt.Year + "_" + dt.Month + "_" + dt.Day + "_" + dt.Hour + "_"+ dt.Minute + "_"+dt.Second+"_" + dt.Millisecond;
                BackupDeviceItem device = new BackupDeviceItem("C:\\"+databaseName+".bak", DeviceType.File);
                dbBackup.Devices.Add(device);
                dbBackup.Script(sqlServer);
                dbBackup.PercentCompleteNotification = 10;
                dbBackup.SqlBackup(sqlServer);
                sqlConn.Close();
                return true;
            }
            catch (Exception exc)
            {
                dbBackup.Abort();
                sqlConn.Close();
                return false;

            }
        }
        public bool RestoreDb(string dbName, string IpHost)
        {
            using (var sqlConn = new SqlConnection("Data Source=" + IpHost + ";Initial Catalog=" + dbName + ";User ID=sa;Password=Admin123456!"))
            {
                ServerConnection svCon = new ServerConnection(sqlConn);
                Server svr = new Server(svCon);
                sqlConn.Open();
                sqlConn.ChangeDatabase("master");
                string testFolder = @"C:\";
                string databaseName = dbName;
                Restore restore = new Restore();
                restore.Action = RestoreActionType.Database;
                string fileName = string.Format("{0}\\{1}.bak", testFolder, databaseName);
                BackupDeviceItem backupItemDevice = new BackupDeviceItem(fileName, DeviceType.File);
                restore.Devices.AddDevice(fileName, DeviceType.File);
                restore.Database = databaseName;
                restore.ReplaceDatabase = true;
                restore.PercentCompleteNotification = 10;
                svr.KillAllProcesses(databaseName);
                restore.SqlRestore(svr);
                return true;
                svr = null;
                sqlConn.Close();
            }
        }

        public string[] getDBNameList()
        {
            try
            {
                var sqlConn = new SqlConnection("Data Source=10.0.0.1;Initial Catalog=Vincop;User ID=dev;Password=Admin123456!");
                var sqlServer = new Server(new ServerConnection(sqlConn));
                var dbnamelist = new List<string>();
                foreach (Database db in sqlServer.Databases)
                {
                    dbnamelist.Add(db.Name);
                }

                return dbnamelist.ToArray();
                //sqlConn.Close();
            }
            catch (Exception exc)
            {
                return null;
            }
        }


        
        
        public Tuple<int, TinTucView[]> getListNewsPaging(string _keyword, int _fromGet, int _toGet = 100)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                db.Configuration.ProxyCreationEnabled = false;
                var _tintucView = new List<TinTucView>();

                db.TinTucs.ToList().ForEach(s =>
                {
                    _tintucView.Add(new TinTucView()
                    {
                        ID = s.ID,
                        NgayTao = s.NgayTao,
                        NoiDung = s.NoiDung,
                        Status = (bool)s.Status,
                        TenBaiViet = s.TenBaiViet,
                        DanhMuc_Id = (int)s.DanhMuc_Id,
                        NguoiTao = s.NguoiTao,
                        TenDanhMuc = db.DanhMucTinTucs.Where(d => d.ID == s.DanhMuc_Id).FirstOrDefault().Name == null ? "" : db.DanhMucTinTucs.Where(d => d.ID == s.DanhMuc_Id).FirstOrDefault().Name
                    });
                });
                if (!String.IsNullOrEmpty(_keyword))
                    _tintucView = _tintucView.Where(s => s.TenBaiViet.Contains(_keyword)).ToList();
                int _totalItem = _tintucView.Count();
                if (_fromGet == 0)
                    return new Tuple<int, TinTucView[]>(_totalItem, _tintucView.OrderBy(s => s.ID).Skip(0).Take(_toGet).ToArray());
                else
                    return new Tuple<int, TinTucView[]>(_totalItem, _tintucView.OrderBy(s => s.ID).Skip(_fromGet).Take(_toGet).ToArray());
            }
        }
        public Tuple<int, TinTucView[]> getIntroListNews(string _keyword, int _fromGet, int _toGet = 100)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                db.Configuration.ProxyCreationEnabled = false;
                var _tintucView = new List<TinTucView>();

                db.TinTucs.Where(w => w.DanhMuc_Id != 4).ToList().ForEach(s =>
                {
                    string[] result = s.NoiDung.Split(new string[] { "<hr />" }, StringSplitOptions.None);
                    string content = Regex.Replace(result[0], @"<[^>]+>|&nbsp;", "").Trim();
                    string introimg = Regex.Match(result[0], "<img.+?src=[\"'](.+?)[\"'].*?>", RegexOptions.IgnoreCase).Groups[1].Value;
                    _tintucView.Add(new TinTucView()
                    {
                        ID = s.ID,
                        NgayTao = s.NgayTao,
                        NoiDung = content,
                        IntroImg = introimg != "" ? introimg : "/Img/cover.jpg",
                        Status = (bool)s.Status,
                        TenBaiViet = s.TenBaiViet,
                        DanhMuc_Id = (int)s.DanhMuc_Id,
                        NguoiTao = s.NguoiTao,
                        TenDanhMuc = db.DanhMucTinTucs.Where(d => d.ID == s.DanhMuc_Id).FirstOrDefault().Name == null ? "" : db.DanhMucTinTucs.Where(d => d.ID == s.DanhMuc_Id).FirstOrDefault().Name

                    });
                });
                if (!String.IsNullOrEmpty(_keyword))
                    _tintucView = _tintucView.Where(s => s.TenBaiViet.Contains(_keyword)).ToList();
                int _totalItem = _tintucView.Count();
                if (_fromGet == 0)
                    return new Tuple<int, TinTucView[]>(_totalItem, _tintucView.OrderBy(s => s.ID).Skip(0).Take(_toGet).ToArray());
                else
                    return new Tuple<int, TinTucView[]>(_totalItem, _tintucView.OrderBy(s => s.ID).Skip(_fromGet).Take(_toGet).ToArray());
            }
        }
        public TinTucView finNewsById(int Id)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                db.Configuration.ProxyCreationEnabled = false;
                var _news = db.TinTucs.Where(s => s.ID == Id).FirstOrDefault();
                var _danhmuc = db.DanhMucTinTucs.Where(w => w.ID == _news.DanhMuc_Id).FirstOrDefault();
                return new TinTucView() { ID = _news.ID, DanhMuc_Id = (int)_news.DanhMuc_Id, NgayTao = _news.NgayTao, NguoiTao = _news.NguoiTao, NoiDung = _news.NoiDung, Status = (bool)_news.Status, TenDanhMuc = _danhmuc.Name, TenBaiViet = _news.TenBaiViet };

            }
        }

        public TinTucView getNewsDetail(int Id)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                db.Configuration.ProxyCreationEnabled = false;
                var _news = db.TinTucs.Where(s => s.ID == Id).FirstOrDefault();
                var _danhmuc = db.DanhMucTinTucs.Where(w => w.ID == _news.DanhMuc_Id).FirstOrDefault();

                string[] result = _news.NoiDung.Split(new string[] { "<hr />" }, StringSplitOptions.None);

                return new TinTucView()
                {
                    ID = _news.ID,
                    DanhMuc_Id = (int)_news.DanhMuc_Id,
                    NgayTao = _news.NgayTao,
                    NguoiTao = _news.NguoiTao,
                    NoiDung = result[1],
                    Status = (bool)_news.Status,
                    TenDanhMuc = _danhmuc.Name,
                    TenBaiViet = _news.TenBaiViet
                };

            }
        }
        public DanhMucTinTuc[] getAllDanhMuc()
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                db.Configuration.ProxyCreationEnabled = false;
                var _result = db.DanhMucTinTucs.Where(s => s.Status == true);
                return _result.ToArray();
            }
        }
        public bool EditNews(TinTucView tmEdit)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                db.Configuration.ProxyCreationEnabled = false;
                var newsEdit = db.TinTucs.Where(s => s.ID == tmEdit.ID).FirstOrDefault();
                if (newsEdit != null)
                {
                    newsEdit.DanhMuc_Id = tmEdit.DanhMuc_Id;
                    newsEdit.TenBaiViet = tmEdit.TenBaiViet;
                    newsEdit.NoiDung = tmEdit.NoiDung;
                    newsEdit.Status = tmEdit.Status;
                    db.SaveChanges();
                    return true;
                }
                else
                    return false;
            }
        }
        public bool AddNews(TinTucView tmNew)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                db.Configuration.ProxyCreationEnabled = false;
                var newsEdit = new TinTuc();
                try
                {
                    newsEdit.DanhMuc_Id = tmNew.DanhMuc_Id;
                    newsEdit.TenBaiViet = tmNew.TenBaiViet;
                    newsEdit.NoiDung = tmNew.NoiDung;
                    newsEdit.Status = true;
                    newsEdit.NgayTao = DateTime.Now;
                    newsEdit.NguoiTao = tmNew.NguoiTao;
                    db.TinTucs.Add(newsEdit);

                    db.SaveChanges();
                    return true;
                }
                catch (Exception ex)
                {
                    return false;
                }
            }
        }
        public bool DeleteNews(int id)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                db.Configuration.ProxyCreationEnabled = false;
                var _del = db.TinTucs.Where(s => s.ID == id).FirstOrDefault();
                if (_del != null)
                {
                    db.TinTucs.Remove(_del);
                    db.SaveChanges();
                    return true;
                }
                else
                    return false;
            }

        }
        public bool AddCategoryNews(DanhMucTinTuc dmNew)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                db.Configuration.ProxyCreationEnabled = false;
                try
                {
                    dmNew.Status = true;
                    db.DanhMucTinTucs.Add(dmNew);
                    db.SaveChanges();
                    return true;
                }
                catch (Exception ex)
                {
                    return false;
                }
            }

        }
        public bool EditCategoryNews(DanhMucTinTuc dmNew)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                db.Configuration.ProxyCreationEnabled = false;
                var dmEdit = db.DanhMucTinTucs.Where(s => s.ID == dmNew.ID).FirstOrDefault();
                if (dmEdit != null)
                {
                    dmEdit.Name = dmNew.Name;
                    dmEdit.Status = dmNew.Status;
                    db.SaveChanges();
                    return true;
                }
                else
                    return false;
            }
        }
        public bool DeletCategoryNews(int Id)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                db.Configuration.ProxyCreationEnabled = false;
                var dmEdit = db.DanhMucTinTucs.Where(s => s.ID == Id).FirstOrDefault();
                if (dmEdit != null)
                {
                    db.DanhMucTinTucs.Remove(dmEdit);
                    db.SaveChanges();
                    return true;
                }
                else
                    return false;
            }
        }
        public DanhMucTinTuc findDanhMucTinTucById(int Id)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                db.Configuration.ProxyCreationEnabled = false;
                var dmEdit = db.DanhMucTinTucs.Where(s => s.ID == Id).FirstOrDefault();
                if (dmEdit != null)
                {
                    return dmEdit;
                }
                else
                    return null;
            }
        }
        public TinTuc[] getListTinTuc()
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                return db.TinTucs.ToArray();
            }

        }
        public TinTuc findTinTuc(int id)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                return db.TinTucs.Where(w => w.ID == id).SingleOrDefault();
            }

        }
        public void BackupDatabase(string backUpFile)
        {
            string connection = DuAnDaCapEntities.getDomain();
            ServerConnection con = new ServerConnection(@"10.0.0.1");
            Server server = new Server(con);
            Backup source = new Backup();
            source.Action = BackupActionType.Database;
            source.Database = "MyDataBaseName";
            BackupDeviceItem destination = new BackupDeviceItem(backUpFile, DeviceType.File);
            source.Devices.Add(destination);
            source.SqlBackup(server);
            con.Disconnect();
        }
        public void RestoreDatabase(string backUpFile)
        {
            try
            {
                string connection = "10.0.0.1";
                var sqlConn = new SqlConnection("Data Source=" + connection + ";Initial Catalog=" + dbName + ";User ID=sa;Password=Admin123456!");
                ServerConnection con = new ServerConnection(sqlConn);
                Server server = new Server(con);
                Restore destination = new Restore();
                destination.Action = RestoreActionType.Database;
                destination.Database = "LKVMLM2";
                BackupDeviceItem source = new BackupDeviceItem(backUpFile, DeviceType.File);
                destination.Devices.Add(source);
                destination.ReplaceDatabase = true;
                destination.SqlRestore(server);
            }
            catch (Exception ex)
            {
            }
        }
        public PhanQuyenWebAdmin ChoosePermissionForGroup(int Group = 0)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                return db.PhanQuyenWebAdmins.Where(w => w.Group_ID == Group).FirstOrDefault();
            }
        }
        public void AddPhanQuyenChoUser(PhanQuyenWebAdmin c)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                var ChooseGroup = db.PhanQuyenWebAdmins.Where(w => w.Group_ID == c.Group_ID).FirstOrDefault();
                if (ChooseGroup == null)
                {
                    db.PhanQuyenWebAdmins.Add(c);
                    db.SaveChanges();
                }
                else
                {
                    ChooseGroup.QuyenBacKupDuLieu = c.QuyenBacKupDuLieu;
                    ChooseGroup.QuyenCapNhatLogoWebsite = c.QuyenCapNhatLogoWebsite;
                    ChooseGroup.QuyenCauHinhCayHeThong = c.QuyenCauHinhCayHeThong;
                    ChooseGroup.QuyenCauHinhChuKy = c.QuyenCauHinhChuKy;
                    ChooseGroup.QuyenCauHinhChuyenDoi = c.QuyenCauHinhChuyenDoi;
                    ChooseGroup.QuyenCauHinhHoaHong = c.QuyenCauHinhHoaHong;
                    ChooseGroup.QuyenCauHinhThanhVien = c.QuyenCauHinhThanhVien;
                    ChooseGroup.QuyenDoanhThuLoiNhuan = c.QuyenDoanhThuLoiNhuan;
                    ChooseGroup.QuyenKichHoatTv = c.QuyenKichHoatTv;
                    ChooseGroup.QuyenResetData = c.QuyenResetData;
                    ChooseGroup.QuyenRestoreDuLieu = c.QuyenRestoreDuLieu;
                    ChooseGroup.QuyenSuaDuLieu = c.QuyenSuaDuLieu;
                    ChooseGroup.QuyenTaoTinTuc = c.QuyenTaoTinTuc;
                    ChooseGroup.QuyenThanhToan = c.QuyenThanhToan;
                    ChooseGroup.QuyenThemMoi = c.QuyenThemMoi;
                    ChooseGroup.QuyenTinhToanHoaHong = c.QuyenTinhToanHoaHong;
                    ChooseGroup.QuyenXoa = c.QuyenXoa;
                    ChooseGroup.QuyenXuatExcel = c.QuyenXuatExcel;
                    db.SaveChanges();
                }
            }
        }
        public Withdrawal addWithDrawal(int userid, float amount, int payment, int walletId, int fromUser = 0)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                try
                {
                    var feeconfig = db.FeeConfigs.SingleOrDefault();
                    var balance = db.Wallet_Detail.Where(w => w.UserID == userid && w.WalletID == walletId && w.Balance >= amount).SingleOrDefault();
                    if (balance == null) return null;
                    var newwithdraw = new Withdrawal { Amout = Math.Round((double)(amount * (1 - feeconfig.Cashout)), 8), Create_Date = DateTime.Now, Payment_Method = payment, Status = 0, UserId = userid, WalletId = walletId, From_User = fromUser,IsConfirm = false };
                    db.Withdrawals.Add(newwithdraw);
                    var wallet = db.Wallet_Detail.Where(w => w.UserID == userid && w.WalletID == walletId).SingleOrDefault();
                    if (wallet != null)
                    {
                        wallet.Balance -= (double)amount;
                        var walletHistory = new WalletHistory();
                        walletHistory.UserId = userid;
                        walletHistory.Amount = -1 * amount;
                        walletHistory.CreateDate = DateTime.Now;
                        walletHistory.WalletId = walletId;
                        walletHistory.Description = "Withdraw";
                        db.WalletHistories.Add(walletHistory);
                    }

                    db.SaveChanges();
                    return newwithdraw;
                }
                catch (Exception ex)
                {
                    return null;
                }

            }
        }
        public Session GetSession(int UserId)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                db.Configuration.ProxyCreationEnabled = false;
                var session = db.Sessions.Where(w => w.Value == UserId).FirstOrDefault();
                if (session != null)
                {
                    return session;
                }                
            }
            return null;
        }

        public bool DeleteSession(int UserId, string SessionId)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                db.Configuration.ProxyCreationEnabled = false;
                var session = db.Sessions.Where(w => w.Value == UserId && w.Id != SessionId).ToList();
                if (session != null)
                {
                    foreach(Session item in session ){
                        db.Sessions.Remove(item);
                        db.SaveChanges();
                    }
                    return true;
                }
            }
            return false;
        }

        /// <summary>
        /// Check in session
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public bool CheckSession(string Id)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                db.Configuration.ProxyCreationEnabled = false;
                var session = db.Sessions.Find(Id);
                if (session != null)
                {
                    if (session.Expired > DateTime.Now)
                    {
                        this.DeleteSession(session.Value, session.Id);
                        return true;
                    }                    
                }
            }
            return false;
        }

        public bool ClearSessionUser(int UserId)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                db.Configuration.ProxyCreationEnabled = false;
                var session = db.Sessions.Where(w => w.Value == UserId).ToList();
                if (session != null)
                {
                    foreach (Session item in session)
                    {
                        db.Sessions.Remove(item);
                        db.SaveChanges();
                    }
                    return true;
                }
            }
            return false;
        }
        

        public int GetUserIdBySession(string Id)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                db.Configuration.ProxyCreationEnabled = false;
                var session = db.Sessions.Find(Id);
                if (session != null)
                {
                    return session.Value;
                }
            }
            return 0;
        }

        /// <summary>
        /// set session for user
        /// </summary>
        /// <param name="UserId"></param>
        /// <returns></returns>
        public string SetSession(int UserId)
        {
            string connection = DuAnDaCapEntities.getDomain();
            using (var db = DuAnDaCapEntities.initDuAnDaCapEntities(connection))
            {
                db.Configuration.ProxyCreationEnabled = false;
                var session = new Session();
                session.Value = UserId;
                session.Expired = DateTime.Now.AddMinutes(15);
                var strMD5 = UserId.ToString() + session.Expired.ToString() + DateTime.Now.Millisecond.ToString();
                session.Id = this.MD5Hash(strMD5);
                db.Sessions.Add(session);
                db.SaveChanges();
                return session.Id;
            }
        }
        public string MD5Hash(string text)
        {
            MD5CryptoServiceProvider md5 = new MD5CryptoServiceProvider();
            md5.ComputeHash(ASCIIEncoding.ASCII.GetBytes(text));
            byte[] result = md5.Hash;
            StringBuilder strBuilder = new StringBuilder();
            for (int i = 0; i < result.Length; i++)
            {
                strBuilder.Append(result[i].ToString("x2"));
            }
            return strBuilder.ToString();
        }
    }
    public class TinTucView
    {
        public int ID { get; set; }
        public string TenBaiViet { get; set; }
        public int DanhMuc_Id { get; set; }
        public string TenDanhMuc { get; set; }
        public string NoiDung { get; set; }
        public DateTime? NgayTao { get; set; }
        public string NguoiTao { get; set; }
        public bool Status { get; set; }
        public string IntroImg { get; set; }
    }
}
